import pymysql
from flask import Flask, request, render_template, session, redirect, url_for, flash

import settings
app = Flask(__name__)
app.secret_key = 'your_secret_key'
app.config.from_object(settings)

# 连接到MySQL数据库
conn = pymysql.connect(
    host='localhost',   # 数据库服务器地址
    user='root',        # 用户名
    password='123456',  # 密码
    database='db3',     # 数据库名称
    charset='utf8mb4',  # 字符集，推荐使用utf8mb4以支持emoji等特殊字符
    autocommit=True     # 自动提交，如果不希望自动提交则设置为False
)
cur1 = conn.cursor()
cur2 = conn.cursor()


@app.route('/', methods=['GET', 'POST'])
def index():
    return render_template('login.html')


@app.route('/in', methods=['GET', 'POST'])
def main():
    return render_template('MainInterface.html')


@app.route('/chose')
def choose():

    return render_template('BackState.html')


# 假设您有一个函数来获取数据库连接
def get_db_connection():
    conn = pymysql.connect(host='localhost',
                           user='root',
                           password='123456',
                           db='db3',
                           charset='utf8mb4',
                           cursorclass=pymysql.cursors.DictCursor)
    return conn


@app.route('/add_student', methods=['POST'])
def add_student():
    # 获取表单数据
    name =  request.form['name']
    gender = request.form['gender']
    age = request.form['age']
    class_id = request.form['class']
    id = request.form['id']
    dor_num = request.form['dormitory_number']

    # 使用上下文管理器确保资源被正确关闭
    with get_db_connection() as conn:
        with conn.cursor() as cur1:
            try:
                sql = "INSERT INTO studentsinfo(id, name, gender, age, class_id, dormitory_number) VALUES (%s,%s,%s,%s,%s,%s)"
                val = (id, name, gender, age, class_id, dor_num)
                cur1.execute(sql, val)
                conn.commit()
                return '学生信息已添加'
            except Exception as e:
                # 回滚事务并记录错误
                conn.rollback()
                app.logger.error("Error inserting student info: %s", str(e))
                return '学生信息已存在'


@app.route('/update', methods=['POST', "GET"])
def update():
    # 获取表单数据
    name = request.form['newName']
    gender = request.form['newGender']
    age = request.form['newAge']
    class_id = request.form['newClass']
    id = request.form['updateId']

    # 使用上下文管理器确保资源被正确关闭
    with get_db_connection() as conn:
        with conn.cursor() as cur1:
            try:
                # 检查ID是否存在
                cur1.execute("SELECT * FROM StudentsInfo WHERE id = %s", (id,))
                result = cur1.fetchone()
                if result:
                    # 更新学生信息
                    sql = "UPDATE studentsinfo SET name = %s, gender = %s, age = %s, class_id = %s WHERE id = %s"
                    val = (name, gender, age, class_id, id)
                    cur1.execute(sql, val)
                    conn.commit()
                    return '学生信息已更新'
                else:
                    return 'ID不存在，无法更新学生信息'
            except Exception as e:
                # 回滚事务并记录错误
                conn.rollback()
                app.logger.error("Error updating student info: %s", str(e))
                return '内部错误，请稍后再试'


# 删除学生信息的路由
@app.route('/delete', methods=['POST'])
def delete_student():
    # 获取表单数据
    id = request.form['deleteId']

    # 使用上下文管理器确保资源被正确关闭
    with get_db_connection() as conn:
        with conn.cursor() as cur1:
            try:
                # 执行SQL删除语句
                cur1.execute("DELETE FROM StudentsInfo WHERE id = %s", (id,))

                conn.commit()
                return '学生信息已删除'
            except Exception as e:
                # 回滚事务并记录错误
                conn.rollback()
                app.logger.error("Error deleting student info: %s", str(e))
                return '内部错误，请稍后再试'


# @app.route('/search', methods=['POST'])
# def search():
#     # 获取表单数据
#     search_term = request.form.get('id')
#     search_term1 = request.form.get('id')
#     search_term2 = request.form.get('id')
#     # 使用上下文管理器确保资源被正确关闭
#     with get_db_connection() as conn:
#         with conn.cursor() as cur:
#             try:
#                 # 构建精确查询的SQL语句
#                 sql  = "SELECT * FROM StudentsInfo WHERE id = %s or name = %s or dormitory_number = %s"
#                 val = (search_term, search_term1, search_term2)
#                 cur.execute(sql, val)
#                 result = cur.fetchall()
#                 print(result) #变成列表了
#                 if (result is not None):
#                     # 查询学生信息
#                     student_info = result
#                     # 确保返回的数据是一个字典，以便在模板中使用
#                     # student_info_dict = dict(student_info) if student_info else {}
#                     print(student_info)
#                     return render_template("学生信息.html", student_info=student_info)
#                 else:
#                     return 'ID、姓名或宿舍号不存在，无法查询学生信息'
#             except Exception as e:
#                 # 回滚事务并记录错误
#                 conn.rollback()
#                 app.logger.error("Error searching for student info: %s", str(e))
#                 return '内部错误，请稍后再试'

@app.route('/search', methods=['POST'])
def search():
    # 获取表单数据
    search_term = request.form.get('id')
    search_term1 = request.form.get('id')
    search_term2 = request.form.get('id')
    # 使用上下文管理器确保资源被正确关闭
    with get_db_connection() as conn:
        with conn.cursor() as cur:
            try:
                if search_term1 == '*':
                    sql = "SELECT * FROM StudentsInfo"
                    cur.execute(sql)
                    result = cur.fetchall()
                else:
                    # 构建精确查询的SQL语句
                    sql = "SELECT * FROM StudentsInfo WHERE id = %s or name = %s or dormitory_number = %s"
                    val = (search_term, search_term1, search_term2)
                    cur.execute(sql, val)
                    result = cur.fetchall()
                # 确保返回的数据是一个字典，以便在模板中使用
                student_info = result
                if student_info:
                    return render_template("学生信息.html", student_info=student_info)
                else:
                    return 'ID、姓名或宿舍号不存在，无法查询学生信息'
            except Exception as e:
                # 回滚事务并记录错误
                conn.rollback()
                app.logger.error("Error searching for student info: %s", str(e))
                return '内部错误，请稍后再试'



# 登录路由
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')

        # 检查用户名是否存在
        cur1.execute("SELECT password FROM users WHERE username = %s", (username,))
        db_password = cur1.fetchone()[0]  # 获取密码字段

        if db_password == password:
            # 登录成功，将用户信息存储在会话中
            session['logged_in'] = True
            session['user_id'] = username  # 假设用户名是唯一的标识符
            return redirect("/in")
        else:
            flash('无效的用户名或密码。')
    return render_template('login.html')


# 注册路由
@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')

        # 检查用户名是否存在
        cur2.execute("SELECT username FROM users WHERE username = %s", (username,))
        if cur2.fetchone():
            flash('用户名已存在。')
        else:
            # 创建新用户
            cur2.execute("INSERT INTO users (username, password) VALUES (%s, %s)", (username, password))
            conn.commit()
            flash('注册成功！请登录。')
            return redirect(url_for('login'))

    return render_template('register.html')


@app.route('/dialog')
def dialog():
    return render_template('dialog.html')


if __name__ == '__main__':
    app.run()
